import xlwings as xw
import os


def generateExampleExcel(stuInfo, path):  # 根据stuInfo在path内生成样例excel
    examplePath = path + r'/二维码生成数据样例.xlsx'
    if os.path.exists(examplePath):
        return
    app = xw.App(visible=True, add_book=False)  # 创建操作对象
    wb = app.books.add()  # 创建工作簿
    sht = wb.sheets['Sheet1']  # 创建工作表
    sht.range('A1:A3').value = ['ID', '姓名', '数量']  # 写入表头
    data = []
    for i in stuInfo:
        data += [[i['id'], i['name'], str(i['num'])]]
    # print(data)
    sht.range('A2').value = data  # 写入数据
    sht.autofit()
    wb.save(examplePath)
    wb.close()
    app.quit()
    return examplePath


def analyseExcel(path):  # 读取path指向的文件，返回stuInfo
    app = xw.App()
    wb = app.books.open(path)
    sheet = wb.sheets['Sheet1']
    stuInfo = []
    line = 2
    while 1:
        uid = str(sheet['A'+str(line)].options(numbers=int).value)
        # print(line, uid)
        if uid == 'None':
            break
        name = str(sheet['B'+str(line)].value)
        num = int(str(sheet['C'+str(line)].options(numbers=int).value))
        stuInfo += [{'id': uid, 'name': name, 'num': num}]
        line += 1
    wb.close()
    app.quit()
    return stuInfo
